package com.hctforgreen.greenservice.ctr;

import java.util.ArrayList;
import java.util.List;

import android.app.Activity;
import android.content.Context;
import android.database.Cursor;

import com.hctforgreen.greenservice.db.DbColumnAdapter;
import com.hctforgreen.greenservice.model.ColumnListEntity;
import com.hctforgreen.greenservice.model.ColumnListEntity.ColumnEntity;
import com.hctforgreen.greenservice.service.HctApplication;
import com.hctforgreen.greenservice.utils.HctConstants;

/**
 * db的控制器
 * 
 * @author Tallen Zhai
 * 
 */
public class DbColumnController {

	private DbColumnAdapter mDbAdapter;

	public DbColumnController(Context context) {
		mDbAdapter = ((HctApplication) context.getApplicationContext())
				.getDbColumnAdapter();
	}

	public DbColumnController(DbColumnAdapter dbAdapter) {
		mDbAdapter = dbAdapter;
	}
	// ============================
	// delete
	// ============================
	public boolean delete(ColumnEntity entity) {
		if (entity._id == 0) {
			entity._id = get_idFromDb(entity);
		}
		if (entity._id != -1) {
			String sql = "delete from " + HctConstants.DB_TABLE_NAME_COLUMN_LST
					+ " where _id=?";
			mDbAdapter.execSQL(sql, new Object[] { entity._id });
			return true;
		}
		return false;
	}

	// ============================
	// Entity insertOrUpdate
	// ============================
	public boolean insertOrUpdate(ColumnEntity entity) {
		if (isExists(entity)) {
			update(entity);
		} else {
			insert(entity);
		}
		return true;
	}

	// ============================
	// Entity update
	// ============================
	private void update(ColumnEntity entity) {
		String sql = "update " + HctConstants.DB_TABLE_NAME_COLUMN_LST
				+ " set name='" + entity.name + "'"
				+ ",icon='" + entity.icon + "'" 
				+ " where id='" + entity.id
				+ "'";

		mDbAdapter.execSQL(sql);
	}
	
	private void deleteDeprecatedColumnListEntity(ColumnListEntity listEntity) {
		ColumnListEntity inDbListEntity = getColumnListEntity();
		List<ColumnEntity> deprecatedList = new ArrayList<ColumnEntity>();
		if (inDbListEntity.dataList == null) {
			return;
		}
		for (ColumnEntity inDbEntity : inDbListEntity.dataList) {
			boolean bDeprecated = true;
			for (ColumnEntity inNetEntity : listEntity.dataList) {
				if (inDbEntity.id.equals(inNetEntity.id)) {
					bDeprecated = false;
				}
			}
			if (bDeprecated) {
				deprecatedList.add(inDbEntity);
			}

		}
		for (ColumnEntity entity : deprecatedList) {
			delete(entity);
		}
	}

	// ============================
	// ListEntity insert
	// ============================
	/**
	 * 插入ColumnListEntity
	 * 
	 * @param entities
	 * @return
	 */
	public boolean insert(ColumnListEntity listEntity) {
		boolean bState = false;
		deleteDeprecatedColumnListEntity(listEntity);
		for (ColumnEntity entity : listEntity.dataList) {
			if (insertOrUpdate(entity)) {
				bState = true;
			}
		}
		return bState;
	}

	// ============================
	// Entity insert
	// ============================
	public boolean insert(ColumnEntity entity) {
		if (isExists(entity)) {
			return false;
		} else {
			String sql = "insert into " + HctConstants.DB_TABLE_NAME_COLUMN_LST
					+ " (id," + "icon," + "name)" + " values " + "(?,?,?);";
			mDbAdapter.execSQL(sql, new Object[] { entity.id, entity.icon,
					entity.name });
			return true;
		}
	}

	// ============================
	// Entity isExists
	// ============================
	/**
	 * 判断此条ColumnEntity在db中是否已经存在
	 * 
	 * @param entity
	 * @return
	 */
	public boolean isExists(ColumnEntity entity) {
		String sql = "select count(*) from "
				+ HctConstants.DB_TABLE_NAME_COLUMN_LST + " where id='"
				+ entity.id + "'";
		Cursor cursor = mDbAdapter.rawQuery(sql, null);
		cursor.moveToFirst();
		if (cursor.getInt(0) > 0) {
			cursor.close();
			return true;
		} else {
			cursor.close();
			return false;
		}
	}

	// ============================
	// Entity Lst
	// ============================

	public ColumnListEntity getColumnListEntity(){
		ColumnListEntity listEntity = new ColumnListEntity();
		listEntity.dataList = getColumnEntityLst();
		return listEntity;
	}
	/**
	 * 查询指定详情页的搜索历史列表
	 * 
	 * @param seriesId
	 * @return
	 */
	public ArrayList<ColumnEntity> getColumnEntityLst() {

		Cursor cursor = getColumnLstCursor();

		ArrayList<ColumnEntity> list = new ArrayList<ColumnListEntity.ColumnEntity>();
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
			ColumnEntity entity = new ColumnEntity();
			int _idIndex = cursor.getColumnIndex("_id");
			int idIndex = cursor.getColumnIndex("id");
			int iconIndex = cursor.getColumnIndex("icon");
			int nameIndex = cursor.getColumnIndex("name");

			entity._id = cursor.getInt(_idIndex);
			entity.id = cursor.getString(idIndex);
			entity.icon = cursor.getString(iconIndex);
			entity.name = cursor.getString(nameIndex);

			list.add(entity);
		}
		cursor.close();
		return list;
	}

	// ============================
	// Entity Lst
	// ============================
	
	// ============================
	// Cusrsor
	// ============================

	
	/**
	 * 获取结果游标(Cursor), asc:最早insert的会先被查询到
	 * 
	 * @return
	 */
	private Cursor getColumnLstCursor() {

		String sql = "select * from " + HctConstants.DB_TABLE_NAME_COLUMN_LST
				+ " order by _id asc";
		Cursor cursor = mDbAdapter.rawQuery(sql, null);
		return cursor;
	}

	// ============================
	// get _id
	// ============================
	private int get_idFromDb(ColumnEntity entity) {
		String sql = "select _id from " + HctConstants.DB_TABLE_NAME_COLUMN_LST
				+ " where " + "id='" + entity.id + "'";
		Cursor cursor = mDbAdapter.rawQuery(sql, null);
		cursor.moveToFirst();
		int _idIndex = cursor.getColumnIndex("_id");
		int _id = -1;
		try {
			_id = cursor.getInt(_idIndex);
			cursor.close();
		} catch (Exception e) {
			cursor.close();
		}
		return _id;
	}

}
